<?php
//======================================================================================
//
// Function: Read integrationLog.sqlite
//
// Programmer: AR
// Date : 2025-05-27
//
// Copyright Reeft A/S (c) - 2025
//======================================================================================
//======================================================================================
// Set start time
//======================================================================================
$starttime = microtime(true);
//======================================================================================
// General config
//======================================================================================
include "config/config.php";
//======================================================================================
// Get input
//======================================================================================
if (isset($_REQUEST["cust_number"])) {
$cust_number = $_REQUEST["cust_number"];
} else {
echo "ERROR: cust_number missing";
exit;
}
if (isset($_REQUEST["fromdate"])) {
$fromdate = $_REQUEST["fromdate"];
} else {
$fromdate = '';
}
if (isset($_REQUEST["fromtime"])) {
$fromtime = $_REQUEST["fromtime"];
} else {
$fromtime = '';
}
if (isset($_REQUEST["todate"])) {
$todate = $_REQUEST["todate"];
} else {
$todate = '';
}
if (isset($_REQUEST["totime"])) {
$totime = $_REQUEST["totime"];
} else {
$totime = '';
}
if (isset($_REQUEST["logType"])) {
$logType = $_REQUEST["logType"];
} else {
$logType = '';
}
if (isset($_REQUEST["logCode"])) {
$logCode = $_REQUEST["logCode"];
} else {
$logCode = '';
}
if (isset($_REQUEST["logPgm"])) {
$logPgm = $_REQUEST["logPgm"];
} else {
$logPgm = '';
}
if (isset($_REQUEST["clientID"])) {
$clientID = $_REQUEST["clientID"];
} else {
$clientID = '';
}
if (isset($_REQUEST["dataLine"])) {
$dataLine = $_REQUEST["dataLine"];
} else {
$dataLine = '';
}
//===============================================================
// Pagination fields "local"/datatables
//===============================================================
if (isset($_REQUEST["rpyOffSet"])) {
$rpyOffSet = $_REQUEST["rpyOffSet"];
} else {
$rpyOffSet = 0;
}
if (isset($_REQUEST["rpyPageSize"])) {
$rpyPageSize = $_REQUEST["rpyPageSize"];
} else {
$rpyPageSize = 0;
}
if (isset($_REQUEST["rpyOrderColumn"])) {
$rpyOrderColumn = $_REQUEST["rpyOrderColumn"];
} else {
$rpyOrderColumn = '';
}
//======================================================================================
// Get order by
//======================================================================================
$rpyOrderColumn = str_replace("|", ' ', $rpyOrderColumn);
$aryOrderBy = explode(',', $rpyOrderColumn);
//======================================================================================
// Get session variables
//======================================================================================
include "include/getsession.php";
include "include/sec2hms.php";
include "include/REEFT_date_convert.php";
//======================================================================================
// Set language
//======================================================================================
include "include/set_language.php";
//======================================================================================
// Set defaults
//======================================================================================
$a = 1;
$b = 2;
if ( $a == $b ) {
if ($fromdate == '' ) {
$fromdate = date("Y-m-d",strtotime("-1 month"));
}
if ($fromtime == '' ) {
$fromtime = date("H:i:s");
}
if ($todate == '' ) {
$todate = date("Y-m-d");
}
if ($totime == '' ) {
$totime = date("H:i:s",strtotime("+1 hour"));
}
}
if ( $fromdate <> '' ) {
$fromTimeStamp = $fromdate . ' ' . $fromtime . ':00';
$toTimeStamp = $todate . ' ' . $totime . ':59';
$fromTimeStamp_UTC = REEFT_local_to_utc( $fromTimeStamp );
$toTimeStamp_UTC = REEFT_local_to_utc( $toTimeStamp );
} else {
$fromTimeStamp_UTC = '';
$toTimeStamp_UTC = '';
}
//======================================================================================
// Connect to SQLite database in file
//======================================================================================
$entries_found = 0;
$currentDate = date('Y-m-d');
$currentTime = date('H:i:s');
$returnCode = '00';
$returnMsg = 'Alles ist gut';
// Init
$aryDetail = array();
header('Content-Type: application/json;charset=utf-8');
//======================================================================================
// Connect to some DB
//======================================================================================
include $DFT_CUST_DIR_PATH."/db_connect.php";
//======================================================================================
// Create SQL
//======================================================================================
$recordsTotal = 0;
$sql = "SELECT tableCount as recordsTotal FROM recordCounter WHERE tableName = 'integration_Log'";
include $DFT_CUST_DIR_PATH."/db_run_sql.php";
error_log(date("Y-m-d H:i:s") . " sql - " . $sql . " \n ",3,"__elog.txt"); //Anja
$mySession = print_r($data, true);
error_log(date("Y-m-d H:i:s") . " data recordsTotal - " . $mySession . " \n ",3,"__elog.txt"); //Anja
foreach( $data as $row )
{
$recordsTotal = $row["recordsTotal"];
}
// Make integer
$recordsTotal = intval( $recordsTotal );
// Format it...
$recordsTotalFormat = number_format($recordsTotal,0,",",".");
//======================================================================================
// Create SQL
// $sql_where = "WHERE timeStamp >= '2025-01-15 00:00:00' and timeStamp <= '2025-01-23 23:59:59'";
//======================================================================================
if ( $fromdate <> '' ) {
$sql_where = "WHERE timeStamp >= '$fromTimeStamp_UTC' and timeStamp <= '$toTimeStamp_UTC'";
$sql_where = "WHERE timeStamp BETWEEN '$fromTimeStamp_UTC' and '$toTimeStamp_UTC'";
if ( $logType <> '' ) {
$logType = strtoupper( $logType );
$sql_where = $sql_where . " " . "AND logType = '$logType'";
}
if ( $logCode <> '' ) {
$logCode = strtoupper( $logCode );
$sql_where = $sql_where . " " . "AND logCode = '$logCode'";
}
if ( $logPgm <> '' ) {
//$sql_where = $sql_where . " " . "AND logPgm like '%$logPgm%'";
$sql_where = $sql_where . " " . "AND logPgm = '$logPgm'";
}
if ( $dataLine <> '' ) {
//$sql_where = $sql_where . " " . "AND logPgm like '%$logPgm%'";
$sql_where = $sql_where . " " . "AND dataLine like '%$dataLine%'";
}
}
else
{
$sql_where = '';
}
$sql_order = "order by $rpyOrderColumn, pk_key desc";
$sql_order = "order by timeStamp desc, pk_key desc";
$sql = "select
a.pk_key as a_pk_key,
a.timeStamp as a_timeStamp,
a.logType as a_logType,
a.logCode as a_logCode,
a.logPgm as a_logPgm,
a.dataLine as a_dataLine
FROM integration_Log a
$sql_where
$sql_order
LIMIT $rpyPageSize OFFSET $rpyOffSet
";
//======================================================================================
// Save sql for header
//======================================================================================
$save_sql = $sql;
// echo $sql;
// exit;
//======================================================================================
// mySQL or SQLite (default)
//======================================================================================
include $DFT_CUST_DIR_PATH."/db_run_sql.php";
foreach( $data as $row )
{
$tmpRow = array();
$tmpRow["pk_key"] = $row["a_pk_key"];
$tmpRow["timeStamp"] = $row["a_timeStamp"];
$tmpRow["logType"] = $row["a_logType"];
$tmpRow["logCode"] = $row["a_logCode"];
$tmpRow["logPgm"] = $row["a_logPgm"];
$tmpRow["dataLine"] = $row["a_dataLine"];
$timestamp_LOCAL = REEFT_utc_to_local( $tmpRow["timeStamp"] );
$tmpRow["timeStamp_local"] = $timestamp_LOCAL;
$aryDetail[] = $tmpRow;
$entries_found++;
}
//======================================================================================
// Check if data is found
//======================================================================================
if ( $entries_found == 0 ) {
$returnCode = '99';
$returnMsg = 'No data found';
}
//======================================================================================
// Records filetered
//======================================================================================
$recordsFiltered = $recordsTotal;
if ( $fromdate <> '' ) {
// $sql = "select count(*) as recordsTotal
// FROM main_log
// ";
$sql = "select count(*) as recordsFiltered
FROM integration_Log
$sql_where
";
include $DFT_CUST_DIR_PATH."/db_run_sql.php";
foreach( $data as $row )
{
$recordsFiltered = $row["recordsFiltered"];
}
}
//======================================================================================
// Calculate response time
//======================================================================================
$endtime = microtime(true);
$response_time = $endtime - $starttime;
$response_time = number_format($response_time, 6, '.', '');
$response_time_raw = number_format($response_time, 6, '.', '');
$response_time = '(' . $response_time . ' seconds)';
$response_time_raw = $response_time_raw;
//======================================================================================
// Create header
//======================================================================================
$aryHeader = array();
$aryHeader["rpyCMPNO"] = '';
$aryHeader["rpyCMPNO_Name"] = $rpyCMPNO_Name;
$aryHeader["rpyFile_name"] = 'mainLog';
$aryHeader["rpyFile_description"] = 'Integration mainLog';
$aryHeader["entries_found"] = $entries_found;
$aryHeader["recordsTotal"] = $recordsTotal;
$aryHeader["recordsTotalFormat"] = $recordsTotalFormat;
$aryHeader["recordsFiltered"] = $recordsFiltered;
$aryHeader["currentDate"] = $currentDate;
$aryHeader["currentTime"] = $currentTime;
$aryHeader["returnCode"] = $returnCode;
$aryHeader["returnMsg"] = $returnMsg;
$aryHeader["response_sec"] = $response_time;
$aryHeader["response_sec_raw"] = $response_time_raw;
$aryHeader["fromdate"] = $fromdate;
$aryHeader["fromtime"] = $fromtime;
$aryHeader["todate"] = $todate;
$aryHeader["totime"] = $totime;
$aryHeader["logType"] = $logType;
$aryHeader["logCode"] = $logCode;
$aryHeader["logPgm"] = $logPgm;
$aryHeader["dataLine"] = $dataLine;
$aryHeader["rpyOffSet"] = $rpyOffSet;
$aryHeader["rpyPageSize"] = $rpyPageSize;
$aryHeader["rpyOrderColumn"] = $rpyOrderColumn;
$sql_pretty = str_replace("\r\n", ' ', $save_sql); // remove carriage returns
$sql_pretty = str_replace("\t", ' ', $sql_pretty); // remove carriage returns
$aryHeader["sql"] = $sql_pretty;
$aryHeader["sql_where"] = $sql_where;
// Create array and prepare for json encoding
$returnJson["header"] = $aryHeader;
$returnJson["data"] = $aryDetail;
//======================================================================================
// Paint it black
//======================================================================================
echo(json_encode($returnJson));
?>